package com.hotel.repository;

import com.hotel.config.DatabaseConfig;
import com.hotel.entity.Guest;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

public class GuestRepository {


    public GuestRepository() {}

    public Guest save(Guest guest) {
        String sql = "INSERT INTO guests (name, gender, id_card, phone) VALUES (?,?,?,?)";
        try (Connection conn = DriverManager.getConnection(
                DatabaseConfig.URL,
                DatabaseConfig.USER,
                DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            stmt.setString(1, guest.getName());
            stmt.setString(2, guest.getGender());
            stmt.setString(3, guest.getId_card());
            stmt.setString(4, guest.getPhone());
            stmt.executeUpdate();
            ResultSet rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                guest.setGuest_id(rs.getInt(1));
            }
        } catch (SQLException e) {
            throw new RuntimeException("保存用户失败", e);
        }
        return guest;
    }

    public List<Guest> findAll() {
        String sql = "SELECT * FROM guests";
        List<Guest> guests = new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(
                DatabaseConfig.URL,
                DatabaseConfig.USER,
                DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                guests.add(mapResultSetToGuest(rs));
            }
            return guests;
        } catch (SQLException e) {
            throw new RuntimeException("查询用户列表失败", e);
        }
    }

    public void delete(int id) {
        String sql = "DELETE FROM guests WHERE guest_id =?";
        try (Connection conn = DriverManager.getConnection(
                DatabaseConfig.URL,
                DatabaseConfig.USER,
                DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.execute("SET FOREIGN_KEY_CHECKS = 0");

            stmt.setInt(1, id);
            stmt.executeUpdate();

            // 重新启用外键检查
            stmt.execute("SET FOREIGN_KEY_CHECKS = 1");

        } catch (SQLException e) {
            throw new RuntimeException("删除用户失败", e);
        }
    }

    public void update(Guest guest) {
        String sql = "UPDATE guests SET name =?, gender =?, id_card =?, phone =? WHERE guest_id =?";
        try (Connection conn = DriverManager.getConnection(
                DatabaseConfig.URL,
                DatabaseConfig.USER,
                DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, guest.getName());
            stmt.setString(2, guest.getGender());
            stmt.setString(3, guest.getId_card());
            stmt.setString(4, guest.getPhone());
            stmt.setInt(5, guest.getGuest_id());
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException("更新用户失败", e);
        }
    }

    public List<Guest> findByNameAndIdCard(String name,String id_card) {
        String sql = "SELECT * FROM guests WHERE name LIKE ? And id_card LIKE ?";
        List<Guest> guests = new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(
                DatabaseConfig.URL,
                DatabaseConfig.USER,
                DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, "%" + name + "%");
            stmt.setString(2, "%" + id_card + "%");
            ResultSet rs = stmt.executeQuery();
            while(rs.next()){
                guests.add(mapResultSetToGuest(rs));
            }
            return guests;
        } catch (SQLException e) {
            throw new RuntimeException("查询用户列表失败", e);
        }
    }

    public Optional<Guest> findByIdCard(String id_card) {
        String sql = "SELECT * FROM guests WHERE id_card =?";
        try (Connection conn = DriverManager.getConnection(
                DatabaseConfig.URL,
                DatabaseConfig.USER,
                DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, id_card);

            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapResultSetToGuest(rs));
                }
            }
            return Optional.empty();
        } catch (SQLException e) {
            throw new RuntimeException("查询用户失败", e);
        }
    }

    private Guest mapResultSetToGuest(ResultSet rs) throws SQLException {
        Guest guest = new Guest();
        guest.setGuest_id(rs.getInt("guest_id"));
        guest.setName(rs.getString("name"));
        guest.setGender(rs.getString("gender"));
        guest.setId_card(rs.getString("id_card"));
        guest.setPhone(rs.getString("phone"));
        return guest;
    }
}
